<?php
/**
 * 报表
 *
 * User: jewens
 */
class Base extends MY_Cron_Controller
{    
    /**
     * 报表
     *
     * php /data/www/shikee/web/cli.php report/Base/index
     */
    public function index()
    {
    	$today = date('Y-m-d');
    	$yesterday = date('Y-m-d', strtotime("-1 day"));
    	$this_week_start = date('Y-m-d', strtotime("this week"));
    	$last_week_start = date('Y-m-d', strtotime("this week -7 days"));
    	$month = date('Y-m-01');
    	
    	$data['time_lat'] = array('1'=>'昨天', '2'=>'本周', '3'=>'上周', '4'=>'本月');
    	
    	//新增用户
    	$this->load->model('user/Model_user','u');
    	
    	$sql = "select count(*) as num from sk_user where create_time between '$yesterday' and '$today'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['new_user'][1] = $rs['num'];
    	
    	$sql = "select count(*) as num from sk_user where create_time > '$this_week_start'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['new_user'][2] = $rs['num'];
    	
    	$sql = "select count(*) as num from sk_user where create_time between '$last_week_start' and '$this_week_start'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['new_user'][3] = $rs['num'];
    	
    	$sql = "select count(*) as num from sk_user where create_time > '$month'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['new_user'][4] = $rs['num'];
    	
    	//充值
    	$sql = "select sum(real_amount) as num from sk_finance_base_log where by_business=6 and create_time between '$yesterday' and '$today'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['recharge'][1] = $rs['num'];
    	
    	$sql = "select sum(real_amount) as num from sk_finance_base_log where by_business=6 and create_time > '$this_week_start'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['recharge'][2] = $rs['num'];
    	
    	$sql = "select sum(real_amount) as num from sk_finance_base_log where by_business=6 and create_time between '$last_week_start' and '$this_week_start'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['recharge'][3] = $rs['num'];
    	
    	$sql = "select sum(real_amount) as num from sk_finance_base_log where by_business=6 and create_time > '$month'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['recharge'][4] = $rs['num'];
    	
    	//消费
    	$sql = "select sum(real_amount) as num from sk_finance_base_log where type=2 and create_time between '$yesterday' and '$today'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['consumption'][1] = $rs['num'];
    	
    	$sql = "select sum(real_amount) as num from sk_finance_base_log where type=2 and create_time > '$this_week_start'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['consumption'][2] = $rs['num'];
    	
    	$sql = "select sum(real_amount) as num from sk_finance_base_log where type=2 and create_time between '$last_week_start' and '$this_week_start'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['consumption'][3] = $rs['num'];
    	
    	$sql = "select sum(real_amount) as num from sk_finance_base_log where type=2 and create_time > '$month'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['consumption'][4] = $rs['num'];
    	
    	//发起竞拍
    	$sql = "select count(*) as num from sk_auction where create_time between '$yesterday' and '$today'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['auction'][1] = $rs['num'];
    	
    	$sql = "select count(*) as num from sk_auction where create_time > '$this_week_start'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['auction'][2] = $rs['num'];
    	
    	$sql = "select count(*) as num from sk_auction where create_time between '$last_week_start' and '$this_week_start'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['auction'][3] = $rs['num'];
    	
    	$sql = "select count(*) as num from sk_auction where create_time > '$month'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['auction'][4] = $rs['num'];
    	
    	//发起竞拍
    	$sql = "select count(*) as num from sk_direct_seeding where create_time between '$yesterday' and '$today'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['live'][1] = $rs['num'];
    	
    	$sql = "select count(*) as num from sk_direct_seeding where create_time > '$this_week_start'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['live'][2] = $rs['num'];
    	
    	$sql = "select count(*) as num from sk_direct_seeding where create_time between '$last_week_start' and '$this_week_start'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['live'][3] = $rs['num'];
    	
    	$sql = "select count(*) as num from sk_direct_seeding where create_time > '$month'";
    	$rs = $this->db->query($sql)->unbuffered_row('array');
    	$data['info']['live'][4] = $rs['num'];
    	
    	//推荐类型
    	$sql = "select recommend_type, count(*) as num from sk_user where create_time between '$yesterday' and '$today' group by recommend_type";
    	$rs = $this->db->query($sql)->result_array('recommend_type');
    	$data['recommend_type'][1] = $rs;
    	
    	$sql = "select recommend_type, count(*) as num from sk_user where create_time > '$this_week_start' group by recommend_type";
    	$rs = $this->db->query($sql)->result_array('recommend_type');
    	$data['recommend_type'][2] = $rs;
    	
    	$sql = "select recommend_type, count(*) as num from sk_user where create_time between '$last_week_start' and '$this_week_start' group by recommend_type";
    	$rs = $this->db->query($sql)->result_array('recommend_type');
    	$data['recommend_type'][3] = $rs;
    	
    	$sql = "select recommend_type, count(*) as num from sk_user where create_time > '$month' group by recommend_type";
    	$rs = $this->db->query($sql)->result_array('recommend_type');
    	$data['recommend_type'][4] = $rs;
    	
    	//充值top10
    	$sql = "select uid, uname, sum(real_amount) as num from sk_finance_base_log where by_business=6 and create_time between '$yesterday' and '$today' group by uid order by num desc limit 10";
    	$rs = $this->db->query($sql)->result_array();
    	$data['recharge'][1] = $rs;
    	
    	$sql = "select uid, uname, sum(real_amount) as num from sk_finance_base_log where by_business=6 and create_time > '$this_week_start' group by uid order by num desc limit 10";
    	$rs = $this->db->query($sql)->result_array();
    	$data['recharge'][2] = $rs;
    	
    	$sql = "select uid, uname, sum(real_amount) as num from sk_finance_base_log where by_business=6 and create_time between '$last_week_start' and '$this_week_start' group by uid order by num desc limit 10";
    	$rs = $this->db->query($sql)->result_array();
    	$data['recharge'][3] = $rs;
    	
    	$sql = "select uid, uname, sum(real_amount) as num from sk_finance_base_log where by_business=6 and create_time > '$month' group by uid order by num desc limit 10";
    	$rs = $this->db->query($sql)->result_array();
    	$data['recharge'][4] = $rs;
    	
    	//消费top10
    	$sql = "select uid, uname, sum(real_amount) as num from sk_finance_base_log where type=2 and create_time between '$yesterday' and '$today' group by uid order by num desc limit 10";
    	$rs = $this->db->query($sql)->result_array();
    	$data['consumption'][1] = $rs;
    	 
    	$sql = "select uid, uname, sum(real_amount) as num from sk_finance_base_log where type=2 and create_time > '$this_week_start' group by uid order by num desc limit 10";
    	$rs = $this->db->query($sql)->result_array();
    	$data['consumption'][2] = $rs;
    	 
    	$sql = "select uid, uname, sum(real_amount) as num from sk_finance_base_log where type=2 and create_time between '$last_week_start' and '$this_week_start' group by uid order by num desc limit 10";
    	$rs = $this->db->query($sql)->result_array();
    	$data['consumption'][3] = $rs;
    	 
    	$sql = "select uid, uname, sum(real_amount) as num from sk_finance_base_log where type=2 and create_time > '$month' group by uid order by num desc limit 10";
    	$rs = $this->db->query($sql)->result_array();
    	$data['consumption'][4] = $rs;
    	
    	//推荐top10
    	$sql = "select recommend_uid as uid, count(*) as num from sk_user where recommend_uid>0 and create_time between '$yesterday' and '$today' group by recommend_uid order by num desc limit 10";
    	$rs = $this->db->query($sql)->result_array();
    	$data['recommend'][1] = $rs;
    	
    	$sql = "select recommend_uid as uid, count(*) as num from sk_user where recommend_uid>0 and create_time > '$this_week_start' group by recommend_uid order by num desc limit 10";
    	$rs = $this->db->query($sql)->result_array();
    	$data['recommend'][2] = $rs;
    	
    	$sql = "select recommend_uid as uid, count(*) as num from sk_user where recommend_uid>0 and create_time between '$last_week_start' and '$this_week_start' group by recommend_uid order by num desc limit 10";
    	$rs = $this->db->query($sql)->result_array();
    	$data['recommend'][3] = $rs;
    	
    	$sql = "select recommend_uid as uid, count(*) as num from sk_user where recommend_uid>0 and create_time > '$month' group by recommend_uid order by num desc limit 10";
    	$rs = $this->db->query($sql)->result_array();
    	$data['recommend'][4] = $rs;

    	
    	if(isset($_GET['show']))
    	{
    		$this->load->view('admin/report/email', $data);
    	}else{
    		$title = '报表 '.date('Y-m-d')." 星期" . mb_substr( "日一二三四五六",date("w"),1,"utf-8" );
	    	$content = $this->load->view('admin/report/email', $data, true);
	    	
	    	$this->load->library('mailer');
	    	//array('lhq@shikee.tv', 'cs@shikee.tv', 'txl@shikee.tv', 'zm@shikee.tv', 'hjp@shikee.tv', 'jsb@shikee.tv', 'dhq@shikee.tv', 'wxw@shikee.tv')
	    	$a = $this->mailer->sendmail(array('hjp@shikee.tv'), $title, $content);
	    	d($a);
	    	echo 'ok';
    	}
    }
}